package org.streets.eis.util;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.BooleanCell;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.wicket.Application;
import org.apache.wicket.Session;
import org.apache.wicket.util.io.ByteArrayOutputStream;
import org.apache.wicket.util.lang.PropertyResolverConverter;

public class ExcelUtils {

	public static ByteArrayOutputStream list2Excel(List<List<Object>> list,String sheetName)  {
		ByteArrayOutputStream excel = new ByteArrayOutputStream();
		
		try{
			WritableWorkbook wwb = Workbook.createWorkbook(excel);
			WritableSheet sheet = wwb.createSheet(sheetName, 0);
		
			int l = list.size();
			if(l>0){	
				PropertyResolverConverter prc = new PropertyResolverConverter(Application.get().getConverterLocator(),Session.get().getLocale());
				
				createHeaderRow(sheet,list.get(0));
			
				for (int i = 1; i < l ; i ++ ){
					createDataRow(sheet, list.get(i),i,prc);        	
				}
			}
			wwb.write();
			wwb.close();
		} catch(Exception e){
			e.printStackTrace();
		}
			        
        return excel;
    }
	
	public static void createHeaderRow(WritableSheet sheet,List<Object> header) throws RowsExceededException,WriteException{
		int l = header.size();
		WritableCellFormat wcf = new WritableCellFormat();  
		WritableFont wf = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);
        wcf.setFont(wf); 

		wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
	    wcf.setBackground(jxl.format.Colour.GRAY_25);
        
		for (int i=0; i< l; i ++){
			String title = header.get(i) != null ? header.get(i).toString() : "";
			Label label = new Label(i, 0, title,wcf);
			sheet.addCell(label);
		}
	}
	
	public static void createDataRow(WritableSheet sheet,List<Object> datas,int rowIndex,PropertyResolverConverter prc) throws RowsExceededException,WriteException{
		int l = datas.size();
		WritableCellFormat wcf = new WritableCellFormat(); 
		wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
		
		for (int i=0; i< l; i ++){
			WritableCell cell = null;
			Object data = datas.get(i);
			String dataValue = (String) prc.convert(data, String.class);
			
			if (data==null){
				cell = new Label(i,rowIndex,"",wcf);
			} else {
				if (java.lang.Number.class.isAssignableFrom(data.getClass())){
					cell = new jxl.write.Number(i,rowIndex,Double.parseDouble(dataValue),wcf); 
				} else {
					 cell = new Label(i,rowIndex,dataValue,wcf);
				}
			}
			sheet.addCell(cell);
		}
	}	

	public static List<List<Object>> excel2List(InputStream is,int sheetIndex){
		List<List<Object>> dataList = new ArrayList<List<Object>>();
				
		try{
			Workbook wb = Workbook.getWorkbook(is);
			Sheet sheet = wb.getSheet(sheetIndex);
		
			int columns = sheet.getColumns();
			int rows = sheet.getRows();
			
			for(int rowIndex = 0 ; rowIndex < rows; rowIndex++){
				List<Object> data = new ArrayList<Object>();

				for (int colIndex= 0; colIndex < columns; colIndex++){
					data.add(getCellData(sheet,colIndex,rowIndex));
				}
				
				dataList.add(data);
			}
			wb.close();
		} catch(Exception e){
			e.printStackTrace();
		}	
		
		return dataList;
	}
	
	public static List<List<Object>> excel2List(InputStream is){
		return excel2List(is,0);	
	}
	
	public static Object getCellData(Sheet sheet,int colIndex,int rowIndex){
		return getCellData(sheet.getCell(colIndex, rowIndex));
	}

	public static Object getCellData(Cell cell){
		CellType cellType = cell.getType();
		Object data = null;

		 if(cellType == CellType.DATE || cellType == CellType.DATE_FORMULA){
			 data =  ((DateCell)cell).getDate();
		 } else if (cellType == CellType.NUMBER || cellType == CellType.NUMBER_FORMULA){
			 data = Double.valueOf(((NumberCell)cell).getValue());
 	     }else if (cellType == CellType.LABEL || cellType == CellType.STRING_FORMULA){
			 data = ((LabelCell)cell).getString();
 	     } else if (cellType == CellType.BOOLEAN || cellType == CellType.BOOLEAN_FORMULA){
			 data = Boolean.valueOf(((BooleanCell)cell).getValue());
		 } else if (cellType == CellType.EMPTY || cellType == CellType.ERROR || cellType == CellType.FORMULA_ERROR) {
			 data = null;
		 }
		 
		return data;
	}
	
}
